package com.shsh.safemanage.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.ArrayList;
import java.util.HashSet;
import java.util.List;
import java.util.Set;

import com.shsh.safemanage.model.Department1;
import com.shsh.safemanage.model.Department2;
import com.shsh.safemanage.model.Department3;
import com.shsh.safemanage.model.JobPost;
import com.shsh.safemanage.model.User;

public class DepartmentDao {
	public List<Department1> getDepartment1List(String nameLike, int departmentType) {
		String sql = "select ID, DEPARTMENT_TYPE, DEPARTMENT1_NAME " + "from department1 where DEPARTMENT1_NAME like '%" + nameLike
				+ "%' and DEPARTMENT_TYPE=?";

		Connection conn = null;
		PreparedStatement pstm = null;

		List<Department1> list = new ArrayList<Department1>();
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);
			pstm.setInt(1, departmentType);

			ResultSet rs = pstm.executeQuery();
			while (rs.next()) {
				Department1 department1 = new Department1();
				department1.setId(rs.getLong("ID"));
				department1.setDepartmentType(rs.getInt("DEPARTMENT_TYPE"));
				department1.setDepartmentName(rs.getString("DEPARTMENT1_NAME"));

				list.add(department1);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}

		return list;
	}

	public List<Department1> getDepartment1List() {
		String sql = "select ID, DEPARTMENT_TYPE, DEPARTMENT1_NAME from department1";
		List<Department1> department1List = new ArrayList<Department1>();

		Connection conn = null;
		PreparedStatement pstm = null;

		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);

			ResultSet rs = pstm.executeQuery();
			while (rs.next()) {
				Department1 depart1 = new Department1();
				depart1.setId(rs.getLong("ID"));
				depart1.setDepartmentType(rs.getInt("DEPARTMENT_TYPE"));
				depart1.setDepartmentName(rs.getString("DEPARTMENT1_NAME"));

				department1List.add(depart1);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}

		return department1List;
	}

	public List<Department1> getDepartment1List(int departType) {
		String sql = "select ID, DEPARTMENT_TYPE, DEPARTMENT1_NAME from department1 where DEPARTMENT_TYPE=?";
		List<Department1> department1List = new ArrayList<Department1>();

		Connection conn = null;
		PreparedStatement pstm = null;

		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);
			pstm.setInt(1, departType);

			ResultSet rs = pstm.executeQuery();
			while (rs.next()) {
				Department1 depart1 = new Department1();
				depart1.setId(rs.getLong("ID"));
				depart1.setDepartmentType(rs.getInt("DEPARTMENT_TYPE"));
				depart1.setDepartmentName(rs.getString("DEPARTMENT1_NAME"));

				department1List.add(depart1);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}

		return department1List;
	}

	public Department1 getDepartment1ByID(Long id) {
		String sql = "select ID, DEPARTMENT_TYPE, DEPARTMENT1_NAME from department1 where ID=?";
		Connection conn = null;
		PreparedStatement pstm = null;

		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);
			pstm.setLong(1, id);

			ResultSet rs = pstm.executeQuery();
			if (rs.next()) {
				Department1 department1 = new Department1();
				department1.setId(id);
				department1.setDepartmentType(rs.getInt("DEPARTMENT_TYPE"));
				department1.setDepartmentName(rs.getString("DEPARTMENT1_NAME"));

				return department1;
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}

		return null;
	}

	public List<Department2> getDepartment2ListByDepartment1ID(Long department1ID) {
		String sql = "select ID, DEPARTMENT1_ID, DEPARTMENT2_NAME, IS_KESHI from department2 where DEPARTMENT1_ID=?";
		List<Department2> department2List = new ArrayList<Department2>();
		Connection conn = null;
		PreparedStatement pstm = null;

		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);
			pstm.setLong(1, department1ID);

			ResultSet rs = pstm.executeQuery();
			while (rs.next()) {
				Department2 depart2 = new Department2();
				depart2.setId(rs.getLong("ID"));
				depart2.setDepartment1ID(rs.getLong("DEPARTMENT1_ID"));
				depart2.setDepartment2Name(rs.getString("DEPARTMENT2_NAME"));
				depart2.setKeshi(rs.getBoolean("IS_KESHI"));
				department2List.add(depart2);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}

		return department2List;
	}

	public Department2 getDepartment2ByID(Long id) {
		String sql = "select ID, DEPARTMENT1_ID, DEPARTMENT2_NAME, IS_EQUIP, IS_MANAGE_GROUP, EQUIP_ID, MANAGE_GROUP_ID "
				+ "from department2 where ID=?";
		Connection conn = null;
		PreparedStatement pstm = null;

		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);
			pstm.setLong(1, id);

			ResultSet rs = pstm.executeQuery();
			if (rs.next()) {
				Department2 depart2 = new Department2();
				depart2.setId(rs.getLong("ID"));
				depart2.setDepartment1ID(rs.getLong("DEPARTMENT1_ID"));
				depart2.setDepartment2Name(rs.getString("DEPARTMENT2_NAME"));
				depart2.setEquip(rs.getBoolean("IS_EQUIP"));
				depart2.setManageGroup(rs.getBoolean("IS_MANAGE_GROUP"));
				depart2.setEquipID(rs.getLong("EQUIP_ID"));
				depart2.setManageGroupID(rs.getLong("MANAGE_GROUP_ID"));

				return depart2;
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}

		return null;
	}

	public List<Department3> getDepartment3ListByDepartment12ID(Long depart1ID, Long depart2ID) {
		String sql = "select ID, DEPARTMENT1_ID, DEPARTMENT2_ID, DEPARTMENT3_NAME "
				+ "from department3 where DEPARTMENT1_ID=? and DEPARTMENT2_ID=?";
		List<Department3> department3List = new ArrayList<Department3>();
		Connection conn = null;
		PreparedStatement pstm = null;

		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);
			pstm.setLong(1, depart1ID);
			pstm.setLong(2, depart2ID);

			ResultSet rs = pstm.executeQuery();
			while (rs.next()) {
				Department3 depart3 = new Department3();
				depart3.setId(rs.getLong("ID"));
				depart3.setDepartment1ID(rs.getLong("DEPARTMENT1_ID"));
				depart3.setDepartment2ID(rs.getLong("DEPARTMENT2_ID"));
				depart3.setDepartment3Name(rs.getString("DEPARTMENT3_NAME"));

				department3List.add(depart3);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}

		return department3List;
	}

	public Department3 getDepartment3ByID(Long id) {
		String sql = "select ID, DEPARTMENT1_ID, DEPARTMENT2_ID, DEPARTMENT3_NAME, ROLE_ID from department3 where ID=?";
		Connection conn = null;
		PreparedStatement pstm = null;

		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);
			pstm.setLong(1, id);

			ResultSet rs = pstm.executeQuery();
			if (rs.next()) {
				Department3 depart3 = new Department3();
				depart3.setId(rs.getLong("ID"));
				depart3.setDepartment1ID(rs.getLong("DEPARTMENT1_ID"));
				depart3.setDepartment2ID(rs.getLong("DEPARTMENT2_ID"));
				depart3.setDepartment3Name(rs.getString("DEPARTMENT3_NAME"));
				depart3.setRoleID(rs.getLong("ROLE_ID"));

				return depart3;
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}

		return null;
	}

	public List<JobPost> getJobPostListByDepartmentID(Long depart1ID, Long depart2ID) {
		String sql = "select ID, DEPARTMENT1_ID, DEPARTMENT2_ID, POST_NAME, JOB_TITLE_ID from job_post "
				+ "where DEPARTMENT1_ID=? and DEPARTMENT2_ID=?";
		List<JobPost> jobPostList = new ArrayList<JobPost>();
		Connection conn = null;
		PreparedStatement pstm = null;

		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);
			pstm.setLong(1, depart1ID);
			pstm.setLong(2, depart2ID);

			ResultSet rs = pstm.executeQuery();
			while (rs.next()) {
				JobPost jobPost = new JobPost();
				jobPost.setId(rs.getLong("ID"));
				jobPost.setDepartment1ID(rs.getLong("DEPARTMENT1_ID"));
				jobPost.setDepartment2ID(rs.getLong("DEPARTMENT2_ID"));
				jobPost.setPostName(rs.getString("POST_NAME"));
				jobPost.setJobTitleID(rs.getInt("JOB_TITLE_ID"));

				jobPostList.add(jobPost);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}

		return jobPostList;
	}

	public boolean addUserDepartment1Mapping(User registerUser, Set<Department1> department1Set) {
		String selectSql = "select ID from user_department1_mapping where EMPLOYEE_ID=? and CORRESPONDED_DEPARTMENT1_ID=?";
		String sql = "insert into user_department1_mapping(EMPLOYEE_ID, CORRESPONDED_DEPARTMENT1_ID) values(?, ?)";

		Connection conn = null;
		PreparedStatement pstm = null;
		PreparedStatement pstm2 = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(selectSql);
			pstm2 = conn.prepareStatement(sql);
			for (Department1 depart1 : department1Set) {
				pstm.setLong(1, registerUser.getId());
				pstm.setLong(2, depart1.getId());

				ResultSet rs = pstm.executeQuery();
				if (!rs.next()) {
					pstm2.setLong(1, registerUser.getId());
					pstm2.setLong(2, depart1.getId());

					pstm2.executeUpdate();
				}
			}

			return true;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}

		return false;
	}

	public boolean deleteUserMappingDepartment1s(Long userID) {
		String sql = "delete from user_department1_mapping where EMPLOYEE_ID=?";

		Connection conn = null;
		PreparedStatement pstm = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);

			pstm.setLong(1, userID);
			pstm.executeUpdate();

			return true;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}

		return true;
	}

	public boolean addDepartment2(Department2 depart2) {
		String sql = "insert into department2(DEPARTMENT1_ID, DEPARTMENT2_NAME, [IS_EQUIP], [IS_MANAGE_GROUP], [EQUIP_ID], [MANAGE_GROUP_ID], [IS_KESHI]) "
				+ " values(?, ?, ?, ?, ?, ?, ?)" + " select @@identity as DEPARTMENT2_ID";

		Connection conn = null;
		PreparedStatement pstm = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
			conn.setAutoCommit(false);

			pstm.setLong(1, depart2.getDepartment1ID());
			pstm.setString(2, depart2.getDepartment2Name());
			pstm.setBoolean(3, false);
			pstm.setBoolean(4, false);
			pstm.setNull(5, Types.INTEGER);
			pstm.setNull(6, Types.INTEGER);
			pstm.setBoolean(7, true);
			pstm.executeUpdate();

			ResultSet rs = pstm.getGeneratedKeys();
			if (rs.next()) {
				depart2.setId(rs.getLong("DEPARTMENT2_ID"));
			}
			conn.commit();

			conn.setAutoCommit(true);
			return true;
		} catch (Exception e) {
			e.printStackTrace();
			try {
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}

		return true;
	}

	public boolean addDepartment3(Department3 depart3) {
		String sql = "insert into department3(DEPARTMENT1_ID, DEPARTMENT2_ID, DEPARTMENT3_NAME, ROLE_ID) values(?, ?, ?, ?)";

		Connection conn = null;
		PreparedStatement pstm = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);
			conn.setAutoCommit(false);

			pstm.setLong(1, depart3.getDepartment1ID());
			pstm.setLong(2, depart3.getDepartment2ID());
			pstm.setString(3, depart3.getDepartment3Name());
			pstm.setLong(4, depart3.getRoleID());
			pstm.executeUpdate();

			conn.commit();

			conn.setAutoCommit(true);
			return true;
		} catch (Exception e) {
			e.printStackTrace();
			try {
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}

		return true;
	}

	public boolean deleteDepartment3(Long depart3ID) {
		String sql = "delete from department3 where ID=?";

		Connection conn = null;
		PreparedStatement pstm = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);
			conn.setAutoCommit(false);

			pstm.setLong(1, depart3ID);
			pstm.executeUpdate();

			conn.commit();

			conn.setAutoCommit(true);
			return true;
		} catch (Exception e) {
			e.printStackTrace();
			try {
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}

		return true;
	}
	
	public boolean deleteDepartment2(Long depart2ID) {
		String sql = "delete from department2 where ID=?";

		Connection conn = null;
		PreparedStatement pstm = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);
			conn.setAutoCommit(false);

			pstm.setLong(1, depart2ID);
			pstm.executeUpdate();

			conn.commit();

			conn.setAutoCommit(true);
			return true;
		} catch (Exception e) {
			e.printStackTrace();
			try {
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}

		return true;
	}

	public List<User> getUserIDsByDepartment2IDandRoleType(Long depart2ID, int roleTypes) {

		String sql = "select employee.ID, NAME from employee "
				+ "left outer join department3 on employee.DEPARTMENT2_ID=department3.DEPARTMENT2_ID and employee.DEPARTMENT3_ID=department3.ID "
				+ "left outer join [role] on department3.ROLE_ID=[role].ID "
				+ "where employee.DEPARTMENT2_ID=? and ([role].ROLE_TYPE & ?) > 0";

		List<User> users = new ArrayList<User>();

		Connection conn = null;
		PreparedStatement pstm = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);

			pstm.setLong(1, depart2ID);
			pstm.setInt(2, roleTypes);
			ResultSet rs = pstm.executeQuery();
			while (rs.next()) {
				User user = new User();
				user.setId(rs.getLong("ID"));
				user.setName(rs.getString("NAME"));

				users.add(user);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}

		return users;
	}

	public List<User> getUserIDsByDepartment1IDandRoleType(Long depart1ID, int roleTypes) {
		String sql = "select distinct employee.ID, NAME from employee "
				+ "left outer join job_post on employee.DEPARTMENT2_ID=job_post.DEPARTMENT2_ID "
				+ "left outer join role on job_post.ROLE_ID=role.ID " + "where employee.DEPARTMENT1_ID=? and (role.ROLE_TYPE & ?) > 0";
		List<User> users = new ArrayList<User>();

		Connection conn = null;
		PreparedStatement pstm = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);

			pstm.setLong(1, depart1ID);
			pstm.setInt(2, roleTypes);
			ResultSet rs = pstm.executeQuery();
			while (rs.next()) {
				User user = new User();
				user.setId(rs.getLong("ID"));
				user.setName(rs.getString("NAME"));
				users.add(user);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}

		return users;
	}

	public List<User> getUsersByDepartment2ID(Long depart2ID, boolean isRegister, int roleType) {
		String sql = "select employee.ID, employee.NAME from employee "
				+ "left outer join department3 on employee.DEPARTMENT3_ID=department3.ID "
				+ "left outer join [role] on department3.ROLE_ID=[role].ID "
				+ "where employee.DEPARTMENT2_ID=? and IS_REGISTERED=? and ([role].ROLE_TYPE & ?) > 0";
		List<User> users = new ArrayList<User>();

		Connection conn = null;
		PreparedStatement pstm = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);

			pstm.setLong(1, depart2ID);
			pstm.setBoolean(2, isRegister);
			pstm.setInt(3, roleType);

			ResultSet rs = pstm.executeQuery();
			while (rs.next()) {
				User user = new User();
				user.setId(rs.getLong("ID"));
				user.setName(rs.getString("NAME"));

				users.add(user);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}

		return users;
	}

	public boolean updateDepartment3andRoleMapping(Long depart3ID, Long roleID) {
		String sql = "update department3 set department3.ROLE_ID = ? where department3.ID = ?";

		Connection conn = null;
		PreparedStatement pstm = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			conn.setAutoCommit(false);
			pstm = conn.prepareStatement(sql);

			pstm.setLong(1, roleID);
			pstm.setLong(2, depart3ID);

			pstm.executeUpdate();

			conn.commit();
			conn.setAutoCommit(true);
			return true;
		} catch (Exception e) {
			e.printStackTrace();
			try {
				conn.rollback();
			} catch (Exception e1) {
				e1.printStackTrace();
			}
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}

		return false;
	}

	public Department1 getDepart1ByUserID(Long userID) {
		String sql = "select department1.ID, DEPARTMENT_TYPE, DEPARTMENT1_NAME from department1 "
				+ "inner join employee on department1.ID=employee.DEPARTMENT1_ID " + "where employee.ID=?";

		Connection conn = null;
		PreparedStatement pstm = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);
			pstm.setLong(1, userID);

			ResultSet rs = pstm.executeQuery();
			if (rs.next()) {
				Department1 depart1 = new Department1();
				depart1.setId(rs.getLong("ID"));
				depart1.setDepartmentType(rs.getInt("DEPARTMENT_TYPE"));
				depart1.setDepartmentName(rs.getString("DEPARTMENT1_NAME"));

				return depart1;
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}

		return null;
	}

	public Set<Department1> getUserMappingDepartment1Set(Long userID) {
		String sql = "select EMPLOYEE_ID, CORRESPONDED_DEPARTMENT1_ID, DEPARTMENT1_NAME "
				+ "from user_department1_mapping inner join department1 on user_department1_mapping.CORRESPONDED_DEPARTMENT1_ID=department1.ID "
				+ "where EMPLOYEE_ID=?";

		Set<Department1> depart1Set = new HashSet<Department1>();

		Connection conn = null;
		PreparedStatement pstm = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);

			pstm.setLong(1, userID);
			ResultSet rs = pstm.executeQuery();
			while (rs.next()) {
				Department1 depart1 = new Department1();
				depart1.setId(rs.getLong("CORRESPONDED_DEPARTMENT1_ID"));
				depart1.setDepartmentName(rs.getString("DEPARTMENT1_NAME"));

				depart1Set.add(depart1);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}

		return depart1Set;
	}
}
